﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using NRemedy;
using NRemedy.Linq;
using Oracle.DataAccess.Client;

namespace Syncer
{
    public class ChangeImport
    {
        public Dictionary<string, Int32> _ComDic { get; set; }

        public Dictionary<string, Int32> _BsDic { get; set; }

        public void Execute(Param param)
        {
            Log.Info("start change import.");
            //get application config
            string arserver = ConfigurationManager.AppSettings["ARServer"].ToString();
            string user = ConfigurationManager.AppSettings["User"].ToString();
            string pwd = ConfigurationManager.AppSettings["Pwd"].ToString();

            _ComDic = param.ComDic;
            _BsDic = param.BsDic;

            using (ARLoginContext context = new ARLoginContext(arserver, user, pwd))
            {
                ARSet<CHGInfrastructureChange> changes = new ARSet<CHGInfrastructureChange>(context, new ARServerDefaultFactory());
                var query = from i in changes
                            where i.Modifed_Date >= param.StartTime && i.Modifed_Date <= param.EndTime
                            select i;

                using (OracleDataAccess oda = new OracleDataAccess())
                {
                    foreach (var change in query)
                    {
                        string sql = "select Request_ID from Change where Request_ID=:requestId";
                        //incidentNumber must not be null
                        OracleParameter[] _param = new OracleParameter[] { 
                            new OracleParameter("requestId",change.InfrastructureChangeID)
                        };
                        object requestid = oda.ExecuteScalar(sql, _param);
                        //if not exsit,should be insert
                        if (requestid == null)
                        {
                            try
                            {
                                InsertChange(change, oda);
                                Log.Info("Insert " + change.InfrastructureChangeID + " OK.");
                            }
                            catch (Exception ex)
                            {
                                Log.Exception("Insert " + change.InfrastructureChangeID + " failed.", ex);
                            }
                        }
                        else
                        {
                            try
                            {
                                //update incident
                                UpdateChange(change, oda);
                                Log.Info("Update " + change.InfrastructureChangeID + " OK.");
                            }
                            catch (Exception ex)
                            {
                                Log.Exception("Update " + change.InfrastructureChangeID + " failed.", ex);
                            }
                        }
                    }
                }

            }
            Log.Info("change import end.");

        }


        private void InsertChange(CHGInfrastructureChange change, OracleDataAccess oda)
        {
            string insert_sql = @"insert into Change(
                                    Request_ID,
                                    SiteID,
                                    BusinessSystemId,
                                    Status,
                                    PlanStartTime,
                                    PlanEndTime,
                                    StartTime,
                                    EndTime,
                                    CloseCode,
                                    TicketCreateTime,
                                    Impact,
                                    Severity,
                                    Priority,
Firm,
FirmProStatus,
Changesource,
Description,
ASGRP,
ASCHG,
submitter_full_name 
) 
                            values (
                                        :request_id,
                                        :siteid,
                                        :businesssystemid,
                                        :status,
                                        :planStartTime,
                                        :planEndTime,
                                        :startTime,
                                        :endTime,
                                        :closecode,
                                        :ticketcreatetime,
                                        :impact,
                                        :severity,
                                        :priority,
:vendor,
:vendor_status,
:Change_source,
:description,
:asgrp,
:aschg,
:submitter
                                    )";
            OracleParameter[] param = new OracleParameter[] { 
                            new OracleParameter("request_id",change.InfrastructureChangeID),
                            new OracleParameter("status",(int)change.Status),
                            new OracleParameter("planStartTime",change.ScheduledStartDate),
                            new OracleParameter("planEndTime",change.ScheduledEndDate),
                            new OracleParameter("startTime",change.ActualStartDate),
                            new OracleParameter("endTime",change.ActualEndDate),
                            new OracleParameter("closecode",(int)change.cChr_EndCode),
                            new OracleParameter("ticketcreatetime",change.Create_Date),
                            new OracleParameter("impact",(int)change.Impact),
                            new OracleParameter("severity",(int)change.Urgency),
                            new OracleParameter("priority",(int)change.Priority),
                            new OracleParameter("vendor",change.cChr_Vendor),
                            new OracleParameter("vendor_status",(int)change.Vendor_Assignment_Status),
                             new OracleParameter("Change_source",(int)change.DDl_ChangeSource),
                             new OracleParameter("description",change.Description),
                            new OracleParameter("asgrp",change.ASGRP),
                            new OracleParameter("aschg",change.ASCHG),
                             new OracleParameter("submitter",change.cChr_SubmitterFullName)
                        };
            var plist = param.ToList();

            //find businesssystemid
            if (string.IsNullOrEmpty(change.cChr_Business))
                plist.Add(new OracleParameter("businesssystemid", null));
            else if (!_BsDic.ContainsKey(change.cChr_Business + change.Company))
                plist.Add(new OracleParameter("businesssystemid", null));
                //throw new Exception("Can not match " + change.cChr_Business + change.Company);
            else
                plist.Add(new OracleParameter("businesssystemid", _BsDic[change.cChr_Business + change.Company]));

            //find companyid
            if (change.ASGRP == "中软海晟技术支持组" || change.ASGRP == "海晟技术支持组")
            {
                change.Company = "中软海晟技术支持组";
            }
            if (!_ComDic.ContainsKey(change.Company))
                throw new Exception("Can not match " + change.Company);
            plist.Add(new OracleParameter("siteid", _ComDic[change.Company]));

            oda.ExecuteCommand(insert_sql, plist.ToArray());
        }

        private void UpdateChange(CHGInfrastructureChange change, OracleDataAccess oda)
        {
            string update_sql = @"update Change set
                                    SiteID=	:siteid,
                                    BusinessSystemId=	:businesssystemid,
                                    Status=	:status,
                                    PlanStartTime=	:planStartTime,
                                    PlanEndTime=	:planEndTime,
                                    StartTime=	:startTime,
                                    EndTime=	:endTime,
                                    CloseCode=	:closecode,
                                    TicketCreateTime=	:ticketcreatetime,
                                    Impact=	:impact,
                                    Severity=	:severity,
                                    Priority=	:priority,
FIRM= :vendor,
FIRMPROSTATUS=:vendor_status,
Changesource =:change_source,
Description=:description,
ASGRP=:asgrp,
ASCHG=:aschg,
submitter_full_name =:submitter
                                    where Request_ID=:request_id
                                    ";
            OracleParameter[] param = new OracleParameter[] { 
                            new OracleParameter("request_id",change.InfrastructureChangeID),
                            new OracleParameter("status",(int)change.Status),
                            new OracleParameter("planStartTime",change.ScheduledStartDate),
                            new OracleParameter("planEndTime",change.ScheduledEndDate),
                            new OracleParameter("startTime",change.ActualStartDate),
                            new OracleParameter("endTime",change.ActualEndDate),
                            new OracleParameter("closecode",(int)change.cChr_EndCode),
                            new OracleParameter("ticketcreatetime",change.Create_Date),
                            new OracleParameter("impact",(int)change.Impact),
                            new OracleParameter("severity",(int)change.Urgency),
                            new OracleParameter("priority",(int)change.Priority),
                            new OracleParameter("vendor",change.cChr_Vendor),
                            new OracleParameter("vendor_status",(int)change.Vendor_Assignment_Status),
                            new OracleParameter("change_source",(int)change.DDl_ChangeSource),
                             new OracleParameter("description",change.Description),
                            new OracleParameter("asgrp",change.ASGRP),
                            new OracleParameter("aschg",change.ASCHG),
                             new OracleParameter("submitter",change.cChr_SubmitterFullName)
                        };
            var plist = param.ToList();

            //find businesssystemid
            if (string.IsNullOrEmpty(change.cChr_Business))
                plist.Add(new OracleParameter("businesssystemid", null));
            else if (!_BsDic.ContainsKey(change.cChr_Business + change.Company))
                plist.Add(new OracleParameter("businesssystemid", null));
            //throw new Exception("Can not match " + change.cChr_Business + change.Company);
            else
                plist.Add(new OracleParameter("businesssystemid", _BsDic[change.cChr_Business + change.Company]));

            //find companyid
            if (change.ASGRP == "中软海晟技术支持组" || change.ASGRP == "海晟技术支持组")
            {
                change.Company = "中软海晟技术支持组";
            }
            if (!_ComDic.ContainsKey(change.Company))
                throw new Exception("Can not match " + change.Company);
            plist.Add(new OracleParameter("siteid", _ComDic[change.Company]));

            oda.ExecuteCommand(update_sql, plist.ToArray());
        }

    }
}
